-- insert a new for sale ad
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'InsertNewForSaleAd') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROC InsertNewForSaleAd
END

GO

CREATE PROCEDURE InsertNewForSaleAd
	@ModelId int,
	@TransmissionTypeId int,
	@YearMade int,
	@Price int,
	@ColorId int,
	@MileAge int,
	@Description nvarchar(MAX),
	@TimePosted smalldatetime,
	@ContactPhone nvarchar(100),
	@ContactEmail nvarchar(100),
	@ContactName nvarchar(100),
	@EditKey nvarchar(30),
	@StatusId int,
	@CityId int,
	@LastEditTime smalldatetime,
	@VehicleConditionId int,
	@ListingTypeId int,
	@PostedByUserAccountId int,
	@NewAdID int OUTPUT
AS
BEGIN
	
	SET NOCOUNT ON
	
	INSERT INTO AdForSale
	(
		ModelId,
		ColorId,
		TransmissionTypeId,
		YearMade,
		Price,
		MileAge,
		Description,
		TimePosted,
		ContactPhone,
		ContactEmail,
		ContactName,
		EditKey,
		StatusId,
		CityId,
		VehicleConditionId,
		ListingTypeId,
		LastEditTime,
		PostedByUserAccountId
	)
	VALUES
	(
		@ModelId,
		@ColorId,
		@TransmissionTypeId,
		@YearMade,
		@Price,
		@MileAge,
		@Description,
		@TimePosted,
		@ContactPhone,
		@ContactEmail,
		@ContactName,
		@EditKey,
		@StatusId,
		@CityId,
		@VehicleConditionId,
		@ListingTypeId,
		@LastEditTime,
		@PostedByUserAccountId
	)
	
	SET @NewAdID = CAST(SCOPE_IDENTITY() AS INT)

	SET NOCOUNT OFF
	
END

GO

-- *********************** END ****************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'GetForSaleAdList') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROC GetForSaleAdList
END

GO

-- *********************** END ****************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'InsertPhoto') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROC InsertPhoto
END

GO
CREATE PROCEDURE InsertPhoto
	@OriginalName nvarchar(100),
	@PathOnServer nvarchar(400),
	@PhotoSize int,
	@UploadDateTime smalldatetime,
	@AdTypeId int,
	@AdId int,
	@DisplayPosition int
AS
BEGIN
	INSERT INTO Photo (OriginalName,PathOnServer,PhotoSize,UploadDateTime,AdTypeId,AdId,DisplayPosition)
	VALUES (@OriginalName,@PathOnServer,@PhotoSize,@UploadDateTime,@AdTypeId,@AdId,@DisplayPosition)
END

-- *********************** END ****************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'GetForSaleAdById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROC GetForSaleAdById
END

GO

CREATE PROCEDURE [GetForSaleAdById]
	@PostID int
AS
BEGIN	
	SELECT 
	 AdForSale.AdId
	,AdForSale.ModelId
	,vVehicleMakeModel.MakeId AS MakeId
	,vVehicleMakeModel.ModelName
	,vVehicleMakeModel.MakeName
	,AdForSale.TransmissionTypeId
	,TransmissionType.TransmissionTypeName
	,YearMade
	,Price
	,MileAge
	,TimePosted
	,ContactPhone
	,ContactEmail
	,ContactName
	,AdForSale.StatusId
	,AdForSale.CityId
	,City.CityName
	,LastEditTime
	,[Description]
	,EditKey
	,PostedByUserAccountId
	,NumClickedOn
	,NumSaved
	,VehicleCondition.VehicleConditionName
	,ListingType.ListingTypeId
	,ListingType.ListingTypeName
	,tProfilePhoto.ProfilePhotoPath

	FROM AdForSale 

	LEFT OUTER JOIN vVehicleMakeModel ON
	vVehicleMakeModel.ModelId = AdForSale.ModelId

	LEFT OUTER JOIN VehicleCondition ON
	VehicleCondition.VehicleConditionId = AdForSale.VehicleConditionId

	LEFT OUTER JOIN City ON
	City.CityId = AdForSale.CityId

	LEFT OUTER JOIN TransmissionType ON
	TransmissionType.TransmissionTypeId = AdForSale.TransmissionTypeId

	LEFT OUTER JOIN ListingType ON 
	ListingType.ListingTypeId = AdForSale.ListingTypeId 
	
	LEFT OUTER JOIN
    (SELECT Photo.AdId, Photo.PathOnServer AS ProfilePhotoPath FROM Photo WHERE Photo.DisplayPosition = 0 ) tProfilePhoto
	ON AdForSale.AdId = tProfilePhoto.AdId

	WHERE AdForSale.AdId = @PostId
END
GO

-- *********************** END ****************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'GetForSaleAdByIdAndEditKey') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROC GetForSaleAdByIdAndEditKey
END

GO

CREATE PROCEDURE [GetForSaleAdByIdAndEditKey]
	@PostID int,
	@EditKey nvarchar(30)
AS
BEGIN	
	SELECT 
	  AdForSale.AdId
	,AdForSale.ModelId
	,vVehicleMakeModel.MakeId AS MakeId
	,vVehicleMakeModel.ModelName
	,vVehicleMakeModel.MakeName
	,AdForSale.TransmissionTypeId
	,TransmissionType.TransmissionTypeName
	,YearMade
	,Price
	,MileAge
	,TimePosted
	,ContactPhone
	,ContactEmail
	,ContactName
	,AdForSale.StatusId
	,AdForSale.CityId
	,City.CityName
	,LastEditTime
	,[Description]
	,EditKey
	,PostedByUserAccountId
	,NumClickedOn
	,NumSaved
	,VehicleCondition.VehicleConditionName
	,ListingType.ListingTypeId
	,ListingType.ListingTypeName
	,tProfilePhoto.ProfilePhotoPath

	FROM AdForSale 

	LEFT OUTER JOIN vVehicleMakeModel ON
	vVehicleMakeModel.ModelId = AdForSale.ModelId

	LEFT OUTER JOIN VehicleCondition ON
	VehicleCondition.VehicleConditionId = AdForSale.VehicleConditionId

	LEFT OUTER JOIN City ON
	City.CityId = AdForSale.CityId

	LEFT OUTER JOIN TransmissionType ON
	TransmissionType.TransmissionTypeId = AdForSale.TransmissionTypeId

	LEFT OUTER JOIN ListingType ON 
	ListingType.ListingTypeId = AdForSale.ListingTypeId 
	
	LEFT OUTER JOIN
    (SELECT Photo.AdId, Photo.PathOnServer AS ProfilePhotoPath FROM Photo WHERE Photo.IsProfilePhoto = 1 ) tProfilePhoto
	ON AdForSale.AdId = tProfilePhoto.AdId

	WHERE AdForSale.AdId = @PostId AND EditKey=@EditKey 
END
GO

-- *********************** END ****************************
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'CreateNewUserAccount') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROC CreateNewUserAccount
END

GO

CREATE PROCEDURE CreateNewUserAccount
	@Email nvarchar(50),
	@Username nvarchar(50),
	@Password nvarchar(50),
	@AccountStateId int,
	@ActivationCode uniqueidentifier,
	@ActivationEmailSentDateTime smalldatetime,
	@NewAccountId int OUTPUT
AS
BEGIN
	
	SET NOCOUNT ON
	INSERT INTO UserAccount
	(
		[Email],
		[Username],
		[Password],
		[AccountStateId],
		[ActivationCode],
		[ActivationEmailSentDateTime]
	)
	VALUES
	(
		@Email,
		@Username,
		@Password,
		@AccountStateId,
		@ActivationCode,
		@ActivationEmailSentDateTime
	)
	
	SET @NewAccountId = CAST(SCOPE_IDENTITY() AS INT)
	
	SET NOCOUNT OFF
END

GO

-- *********************** END ****************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'IncreaseNumberOfTimeClickedOn') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROC IncreaseNumberOfTimeClickedOn
END

GO

CREATE PROCEDURE IncreaseNumberOfTimeClickedOn
	@PostId int
AS
BEGIN
	UPDATE AdForSale SET NumClickedOn = NumClickedOn + 1
	WHERE AdId = @PostId
END

GO

-- *********************** END ****************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'UserSavePost') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROC UserSavePost
END

GO

CREATE PROCEDURE UserSavePost
	@PostId int,
	@UserId int
AS
BEGIN
	IF NOT EXISTS (SELECT PostId FROM UserSavedPost WHERE PostId = @PostId AND UserAccountId=@UserId)
		BEGIN
			INSERT INTO UserSavedPost (UserAccountId,PostId) VALUES (@UserId,@PostId)
			
			UPDATE AdForSale SET NumSaved = NumSaved + 1
			WHERE AdId = @PostId
		END
END

GO

	
